package com.xunfang.demo.dao;

import com.xunfang.demo.entity.QueryData;
import com.xunfang.demo.entity.UserDemo;
import com.xunfang.demo.utils.DruidManger;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    QueryRunner qr = new QueryRunner(DruidManger.getDataSource());

    @Override
    public Integer findUserbyBD(QueryData queryData) {
        String userName = queryData.getQueryName();
        String eid = queryData.getEid();
        String startDate = queryData.getStartDate();
        String endDate = queryData.getEndDate();
        List<Object> list = new ArrayList<>();
        String sql = "select count(1) from education e, userdemo u where e.`eid`=u.`sel` and 1=1";
        if (userName!= null && !userName.isEmpty()){
            sql += " and u.`userName` like ? ";
            list.add("%"+userName+"%");
        }
        if (eid!= null && !eid.isEmpty()){
            sql += " and u.`sel` = ? ";
            list.add(eid);
        }
        if (startDate != null && !startDate.isEmpty()){
            sql += " and u.`birthday` > ? ";
            list.add(startDate);
        }
        if (endDate != null && !endDate.isEmpty()){
            sql += " and u.`birthday`< ? ";
            list.add(endDate);
        }
        try {
            Long lon = qr.query(sql, new ScalarHandler<>(), list.toArray());
            return lon.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int insertUser(UserDemo userDemo) {
        String sql = " insert into userdemo values (null,?,?,?,?,?,?,?) ";
        Object [] params = {userDemo.getUserName(),userDemo.getUserPwd(),userDemo.getSex(),
            userDemo.getChk(),userDemo.getSel(),userDemo.getAddress(),userDemo.getBirthday()};
        try {
            return qr.update(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserDemo selectBySome(String username, String userpwd) {
        String sql = " select * from userdemo where userName = ? and userPwd = ? ";
        try {
            return qr.query(sql,new BeanHandler<UserDemo>(UserDemo.class),username,userpwd);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<UserDemo> selectUserAll() {
        String sql = " select * from userdemo u, education e where u.sel=e.eid  ";
        try {
            return qr.query(sql,new BeanListHandler<UserDemo>(UserDemo.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int delUserById(Integer id) {
        String sql = " delete from userdemo where id = ? ";
        try {
            return qr.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserDemo selectByid(int id) {
        String sql = " select * from userdemo where id = ? ";
        try {
            return qr.query(sql,new BeanHandler<UserDemo>(UserDemo.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateById(UserDemo userDemo) {
        String sql = " update userdemo set  userName=?,userPwd=?,sex=?,chk=?,sel=?,address=?,birthday=? where id=? ";
        try {
            return qr.update(sql,userDemo.getUserName(),userDemo.getUserPwd(),userDemo.getSex(),userDemo.getChk(),userDemo.getSel(),userDemo.getAddress(),userDemo.getBirthday(),userDemo.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<UserDemo> getUserBySome(QueryData queryData) {
        String userName = queryData.getQueryName();
        String eid = queryData.getEid();
        String startDate = queryData.getStartDate();
        String endDate = queryData.getEndDate();
        List<Object> list = new ArrayList<>();
        String sql = "select * from education e, userdemo u where e.`eid`=u.`sel` and 1=1";
        if (userName!= null && !userName.isEmpty()){
            sql += " and u.`userName` like ? ";
            list.add("%"+userName+"%");
        }
        if (eid!= null && !eid.isEmpty()){
            sql += " and u.`sel` = ? ";
            list.add(eid);
        }
        if (startDate != null && !startDate.isEmpty()){
            sql += " and u.`birthday` > ? ";
            list.add(startDate);
        }
        if (endDate != null && !endDate.isEmpty()){
            sql += " and u.`birthday`< ? ";
            list.add(endDate);
        }
        try {
            return qr.query(sql,new BeanListHandler<UserDemo>(UserDemo.class),list.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<UserDemo> findUserBySome(QueryData queryData,Integer p,Integer pageSize) {
        String userName = queryData.getQueryName();
        String eid = queryData.getEid();
        String startDate = queryData.getStartDate();
        String endDate = queryData.getEndDate();
        List<Object> list = new ArrayList<>();
        String sql = "select * from education e, userdemo u where e.`eid`=u.`sel` and 1=1";
        if (userName!= null && !userName.isEmpty()){
            sql += " and u.`userName` like ? ";
            list.add("%"+userName+"%");
        }
        if (eid!= null && !eid.isEmpty()){
            sql += " and u.`sel` = ? ";
            list.add(eid);
        }
        if (startDate != null && !startDate.isEmpty()){
            sql += " and u.`birthday` > ? ";
            list.add(startDate);
        }
        if (endDate != null && !endDate.isEmpty()){
            sql += " and u.`birthday`< ? ";
            list.add(endDate);
        }
        sql += " limit ?,? ";
        list.add((p-1)*pageSize);
        list.add(pageSize);
        try {
            return qr.query(sql,new BeanListHandler<UserDemo>(UserDemo.class),list.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public UserDemo selectUserName(String name) {
        String sql = " select * from userdemo where userName = ? ";
        try {
            return qr.query(sql,new BeanHandler<UserDemo>(UserDemo.class),name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
